package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import util.JDBCUtil;
import domain.EquipmentPost;
import ex.DatabaseException;

public class EquipmentPostDAO {
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;

	private static final String SELECT_EQUIPMENTPOST_BY_UID="select * from equipment_post where uid = ?";
	private static final String CREATE_EQUIPMENTPOST="insert into equipment_post (uid,type,description,city,isForRent,price) values (?, ?, ?, ?, ?, ?) ";
	private static final String EDIT_EQUIPMENTPOST="update equipment_post set type = ?, description = ?, city = ?, isForRent = ?, price = ?, timestamp = ? where epid = ?";
	private static final String DELETE_EQUIPMENTPOST="DELETE FROM equipment_post WHERE epid = ? ";

	//Selection of the EquipmentPost Information using the User ID in the EquipmentPost table
	public EquipmentPost selectEquipmentPostByUid(int uid) throws DatabaseException
	{
		EquipmentPost equipmentPost = null;
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(SELECT_EQUIPMENTPOST_BY_UID);
			stmt.setInt(1, uid);
			rs = stmt.executeQuery();
			if (rs.next()) {
				int type = rs.getInt("type");
				long epid = rs.getLong("epid");
				String description = rs.getString("description");
				String city = rs.getString("city");
				int isForRent = rs.getInt("isForRent");
				int price = rs.getInt("price");
				Timestamp timestamp = rs.getTimestamp("timestamp");
				equipmentPost =new EquipmentPost(epid,uid, type, description, city, isForRent, price, timestamp );
			}
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with retrieving equipment post information! "	+ e.getMessage());
		}
		return equipmentPost;
	}

	//Insertion of a new EquipmentPost to the database
	public void insertEquipmentPost(EquipmentPost equipmentPost) throws DatabaseException {
		
		
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(CREATE_EQUIPMENTPOST);
			stmt.setLong(1, equipmentPost.getUid());
			stmt.setInt(2, equipmentPost.getType());
			stmt.setString(3, equipmentPost.getDescription());
			stmt.setString(4, equipmentPost.getCity());
			stmt.setInt(5, equipmentPost.getIsForRent());
			stmt.setInt(6, equipmentPost.getPrice());	
			
			stmt.execute();			
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with inserting equipment post information! "	+ e.getMessage());
		}
	}
	
	//Editing the EquipmentPost fields
	public void editEquipmentPost(EquipmentPost equipmentPost) throws DatabaseException {
		
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(EDIT_EQUIPMENTPOST);
			stmt.setInt(1, equipmentPost.getType());
			stmt.setString(2, equipmentPost.getDescription());
			stmt.setString(3, equipmentPost.getCity());
			stmt.setInt(4, equipmentPost.getIsForRent());
			stmt.setInt(5, equipmentPost.getPrice());
			stmt.setTimestamp(6, equipmentPost.getTimestamp());	
			stmt.setLong(7, equipmentPost.getEpid());

			stmt.execute();			
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with editing equipment post information! "	+ e.getMessage());
		}
	}
	
	//Removal of an EquipmentPost from the database
	public void deleteEquipmentPost(EquipmentPost equipmentPost) throws DatabaseException{
		
		try {
			conn = JDBCUtil.dequeueConn();
			stmt = conn.prepareStatement(DELETE_EQUIPMENTPOST);
			stmt.setLong(1, equipmentPost.getEpid());

			stmt.execute();		
			stmt.close();
			JDBCUtil.enqueuConn(conn);
		} 
		catch (SQLException e) {
			throw new DatabaseException("Problem with deleting equipment post information! "	+ e.getMessage());
		}
	}
}
